package com.tyl.dao.provider;

import com.tyl.dao.BaseDao;
import com.tyl.pojo.Provider;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProviderDaoImpl implements ProviderDao{

    /**
     *  通过供应商名称、编码获取供应商列表-模糊查询-providerList
     * **/
    public List<Provider> getProviderList(Connection connection, String proCode, String proName)  {
        PreparedStatement pmst=null;
        ResultSet rs= null;
        ArrayList<Provider> providerList = new ArrayList<Provider>();
        if(connection!=null){
            StringBuffer sql = new StringBuffer();
            sql.append("select *from smbms_provider where 1=1");
            ArrayList<String> list = new ArrayList<String >();
            if (proCode!=null&&!proCode.equals("")){
                sql.append(" and proCode like ?");
                list.add(proCode);
            }
            if (proName!=null&&!proName.equals("")){
                sql.append(" and proName like ?");
                list.add(proName);
            }
            Object[] params=list.toArray();
            System.out.println(sql);
            try {
                rs = BaseDao.execute(connection,pmst,sql.toString(),rs,params);

                while (rs.next()){
                    Provider provider=new Provider();
                    provider.setId(rs.getInt("id"));
                    provider.setProCode(rs.getString("proCode"));
                    provider.setProName(rs.getString("proName"));
                    provider.setProDesc(rs.getString("proDesc"));
                    provider.setProContact(rs.getString("proContact"));
                    provider.setProPhone(rs.getString("proPhone"));
                    provider.setProAddress(rs.getString("proAddress"));
                    provider.setProFax(rs.getString("proFax"));
                    provider.setCreationDate(rs.getTimestamp("creationDate"));
                    providerList.add(provider);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(null,pmst,rs);
            }

        }

        return providerList;
    }
    /**
     * 通过proId获取Provider
     * **/
    public Provider getProviderById(Connection connection, String id) throws SQLException {
        PreparedStatement psmt=null;
        ResultSet rs=null;
        Provider provider=null;
        if(connection!=null){
            String sql="select *from smbms_provider where id=?";
            Object[] params={id};
            rs= BaseDao.execute(connection, psmt, sql, null, params);
            while (rs.next()){
                provider=new Provider();
                provider.setId(rs.getInt("id"));
                provider.setProCode(rs.getString("proCode"));
                provider.setProName(rs.getString("proName"));
                provider.setProDesc(rs.getString("proDesc"));
                provider.setProContact(rs.getString("proContact"));
                provider.setProPhone(rs.getString("proPhone"));
                provider.setProAddress(rs.getString("proAddress"));
                provider.setProFax(rs.getString("proFax"));
                provider.setCreationDate(rs.getTimestamp("creationDate"));
            }
            BaseDao.closeResource(null,psmt,rs);
        }
        return provider;
    }

    /**
     * 添加供应商
     * **/
    public int addProvider(Connection connection, Provider provider) {
        PreparedStatement psmt=null;
        int resultRows=0;
        String sql = "insert into smbms_provider (proCode,proName,proDesc, " +
                "proContact,proPhone,proAddress,proFax,createdBy,creationDate) " +
                "values(?,?,?,?,?,?,?,?,?)";
        Object[] params = {provider.getProCode(), provider.getProName(), provider.getProDesc(),
                provider.getProContact(), provider.getProPhone(), provider.getProAddress(),
                provider.getProFax(), provider.getCreatedBy(), provider.getCreationDate()};
        if (connection!=null){
            try {
                resultRows = BaseDao.execute(connection, psmt, sql, params);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(null,psmt,null);
            }
        }
        return resultRows;
    }
    /**
     * 添加供应商
     * **/
    public int deleteProvider(Connection connection, String pId) throws SQLException {
        PreparedStatement pstm=null;
        Object[]pamas={pId};
        int flag=0;
        if (connection!=null){
            String sql="delete from smbms_provider where id=?";
            flag = BaseDao.execute(connection, pstm, sql, pamas);
            BaseDao.closeResource(null,pstm,null);
        }
        return flag;
    }

    /**
     * 修改供应商信息
     * **/
    public int updateProvider(Connection connection, Provider provider) {
        PreparedStatement psmt=null;
        int flag=0;
        if (connection!=null){
            String sql="update smbms_provider set proName=?,proDesc=?,proContact=?," +
                    "proPhone=?,proAddress=?,proFax=?,modifyBy=?,modifyDate=? where id = ? ";
            Object[]params={provider.getProName(),provider.getProDesc(),provider.getProContact(),
            provider.getProPhone(),provider.getProAddress(),provider.getProFax(),provider.getModifyBy(),
            provider.getModifyDate(),provider.getId()};
            System.out.println(sql);
            try {
                flag= BaseDao.execute(connection, psmt, sql, params);
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                BaseDao.closeResource(null,psmt,null);
            }
        }
        return flag;
    }
}
